erDiagram
A }|..|{ B : foo
- L3 MIASHS/Ingémath
- Université Paris Cité
- Année 2023-2024
- Course Homepage
- Moodle
- Pas de documents autorisés
- Pas de téléphone portable
Dans le formalisme Entité-Association, nous utilisons les pattes de corbeau (crowfoot) pour décrire les contraintes de cardinalités.
L’entité A est reliée à l’entité B par l’association foo. Une instance de A peut apparaître de \(1\) à \(n\) fois parmi les instances de l’association foo, de même une instance de B peut apparaître de \(1\) à \(n\) fois parmi les instances de l’association foo.
erDiagram A }|..o| B : bar
L’entité A est reliée à l’entité B par l’association bar. Une instance de A peut apparaître de \(0\) à \(1\) fois parmi les instances de l’association bar, une instance de B peut apparaître de \(1\) à \(n\) fois parmi les instances de l’association bar.
Notez que la contrainte portant sur le nombre de participations des instances d’une entité à une association apparaît à l’opposé de l’entité.
Un système d’information pour l’administration
Un pays (par exemple France) est organisé en régions (par exemple Bretagne, …). Les régions sont identifiées par leur nom. Ces régions sont-elles même divisées en départements (par exemple Finistère). Les départements sont identifiés par de numéros (par exemple 29 pour le Finistère). Les départments possèdent aussi un nom qui les distingue.
Dans chaque département, il y a des communes qui portent des noms, éventuellement précédés d’un article (par exemple Le Mans, Le Bourget, …). Pour chaque département, les communes sont numérotées à l’aide de leur rang dans l’ordre alphabétique construit sur les noms privés de l’éventuel article (dans la Sarthe, 72, Le Mans est au rang 181 entre Mamers et Mansigné).
Pour chaque commune et chaque année, on connaît le montant total perçu au titre de la taxe foncière.
Chaque année, on procède à un recensement dans chaque commune. On en connaît la population.
Chaque région a une préfecture de région, et chaque département a une préfecture de département.
On a cherché à modéliser cette description en formalisme Entité-Association comme suit.
%%{init: { "sequence": { "wrap": true} } }%%
erDiagram
DEPARTEMENT }|..|{ REGION : contient
DEPARTEMENT {
string nom
int departement
}
REGION ||..o| VILLE : prefecture
REGION {
string nom
}
REGION ||..|{ VILLE : contient
DEPARTEMENT |o..|| VILLE : prefecture
VILLE {
string nom
string article
int numero
}
RECENSEMENT ||..|{ VILLE : concerne
RECENSEMENT {
int annee
int population
}
TAXE ||..|{ VILLE : rapporte
TAXE {
int annee
int valeur
}
- Un certain nombre d’erreurs se sont glissées dans ce schéma Entité-Association. Corrigez-le.
- Proposez un identifiant pour chaque entité.
- Désignez les entités faibles.
Soit le schéma \(\mathcal{A}=\left\{\texttt{A},\texttt{B},\texttt{C},\texttt{D},\texttt{E},\texttt{F}\right\}\) et l’ensemble \(\Sigma\) de DF
A,B → C
B → F
C,E → A
F → E
Calculer la clôture/fermeture \(\{\texttt{A},\texttt{B}\} = [\{\texttt{A},\texttt{B}\}]_{\Sigma}^+\) de \(\{\texttt{A},\texttt{B}\}\) en utilisant l’ensemble de DF \(\Sigma\).
Quelles sont les clefs du schéma \(\mathcal{A}\) en utilisant l’ensemble de DF \(\Sigma\) ?
On travaillera par la suite sur le schéma relationnel villes et les tables définies par le code SQL suivant.
CREATE SCHEMA villes;
CREATE TABLE villes.region (
nom varchar NOT NULL PRIMARY KEY,
prefecture_num int4 NULL,
prefecture_dep int4 NULL
);
CREATE TABLE villes.departement (
nom varchar NULL UNIQUE,
departement int4 NOT NULL PRIMARY KEY,
region varchar NULL REFERENCES villes.region(nom)
ON DELETE SET NULL ON UPDATE CASCADE,
numero int4 NULL
);
CREATE TABLE villes.ville (
nom varchar NULL,
article varchar NULL,
departement int4 NOT NULL REFERENCES villes.departement
ON DELETE RESTRICT ON UPDATE CASCADE,
numero int4 NOT NULL,
CONSTRAINT ville_pkey PRIMARY KEY (departement, numero)
);
ALTER TABLE villes.region ADD
CONSTRAINT region_prefecture_fk FOREIGN KEY (prefecture_dep,prefecture_num)
REFERENCES villes.ville(departement,numero)
ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE villes.departement ADD
CONSTRAINT departement_prefecture_fk FOREIGN KEY (departement,numero)
REFERENCES villes.ville(departement,numero)
ON DELETE SET NULL ON UPDATE CASCADE;
CREATE TABLE villes.recensement (
annee year NOT NULL,
departement int4 NOT NULL,
numero int4 NOT NULL,
population int4 NULL,
CONSTRAINT recensement_pk PRIMARY KEY (annee, departement, numero),
CONSTRAINT recensement_fk FOREIGN KEY (departement,numero)
REFERENCES villes.ville(departement,numero)
ON DELETE RESTRICT ON UPDATE CASCADE
);
CREATE TABLE villes.taxe (
annee year NOT NULL,
departement int4 NOT NULL,
numero int4 NOT NULL,
valeur int4 NULL,
CONSTRAINT taxe_pk PRIMARY KEY (annee, departement, numero),
CONSTRAINT taxe_fk FOREIGN KEY (departement,numero)
REFERENCES villes.ville(departement,numero)
ON DELETE RESTRICT ON UPDATE CASCADE
);Toutes les questions qui suivent se rapportent au schéma villes.
Pour chaque question, proposer une requête écrite en algèbre relationnelle ou en SQL.
Lister par région le nombre de communes.
Quel département contient le plus de communes ?
Quel département a connu la plus forte croissance démographique relative entre 2010 et 2020 ?
Pour chaque région, quelle est la proportion de la population qui vit dans des préfectures départementales en 2020 ?